<?php

/*
 * 此方法用于定义大数据分析的基础方法
 * 同比增长率=(本期数-同期数)÷同期数
 * 环比增长率=（本期数-上期数）÷上期数
 */

namespace Common\Controller;

/**
 * Description of BdataController
 *
 * @author chenghui cao
 */
class BdataCoreController {

    /**
     * @创建区间段临时表
     * @param type $region
     */
    protected function createRegionTempTable($key,$region){
        $temp=$key.date('Ymdhis');
        
        $sql='CREATE TABLE '.$temp.'('
            .'[id] [bigint] NOT NULL,'
            .'[code] [nvarchar](100) NULL,'
            .'[title] [nvarchar](255) NULL,'
            .'CONSTRAINT [PK_'.$temp.'] PRIMARY KEY CLUSTERED ' 
            .'('
            .'[id] ASC'
            .')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
            .') ON [PRIMARY]';

        
        M()->execute($sql);
        $sqlStr='insert into '.$temp.'(id,code,title) ';
        $arr = array();

        foreach ($region as $index=>$val){
            $arr[] = ' select '.$index.",'".$val['min'].'-'.$val['max']."'".",'".$val['title']."'";
        }
        $sqlStr .= implode(' union all ', $arr);

        M()->execute($sqlStr);
        return $temp;
    }
    
    /**
     * @根据维度值创建数据源临时表
     * @param type $xAxis
     * @param type $yAxis
     * @param type $decimal
     * @return string
     */
    protected function createTempDataTable($yAxis,$decimal){
        //1.初始化表名
        $temptable='Temp_'.date("Ymdhis");
        $cArr=array();
        
        //X轴数据
        $cArr[]='xAxis varchar(30) ';
        
        
        //Y轴数据
        foreach($yAxis as $key){
            $cArr[]=$key.' varchar(30) ';
        }
        
        //V轴数据
        if($decimal > 0){
            $cArr[]='counts decimal(18,'.$decimal.') ';
        }else{
            $cArr[]='counts int ';
        }
        
        $columnStr=implode(",", $cArr);
        $sql='CREATE TABLE '.$temptable.'('
                .$columnStr
                .')';

        M()->execute($sql);
        
        return $temptable;
    }
    
    /**
     * @将数据写入到临时表
     * @param type $allAxis
     * @param type $vAxis
     * @param type $decimal
     * @param type $OriginData
     * @return string
     */
    protected function insertTempDataTable($allAxis,$vAxis,$decimal,$OriginData){
        //1.创建临时表//返回临时表名
        $temptable=$this->createTempDataTable($allAxis,$vAxis,$decimal);
        
        if(!empty($temptable)){
            $cArr=array();
        
            foreach ($vAxis as $val){
                $v[] = $this->setColumnResultFormat($val['k'], $val['c']);        
            }
            $columns = implode(',', $allAxis).','.implode(',', $v);
            $sql = 'insert into '.$temptable.'('.$columns.') ';
            $sql .= 'select '.$columns.' from ('.$OriginData.')'.' as tt';

            M()->execute($sql);
        }        
        return $temptable;
    }

    /**
     * @删除临时表
     * @param type $tempTableArr
     * @return boolean
     */
    protected function doDropTempTable($tempTableArr){
       if(is_array($tempTableArr)&&!empty($tempTableArr)){
           $sql='';
           foreach($tempTableArr as $val){
              $sql.= "IF  EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='".$val."')";
              $sql.=" DROP TABLE ".$val.";"; 
           }
          M()->execute($sql); 
       }
       return false;
    }
    
    /**
     * @方法：处理日期型字段值
     * @param type $Expression
     * @param type $DataColumn
     * @return type
     */
    private function setDateColumn($Expression,$DataColumn){
        $arr = strtoupper($Expression);
        switch ($arr){
            case 'Y':
                $res = 'YEAR('.$DataColumn.')';
                break;
            case 'YM':
                $res = 'MONTH('.$DataColumn.')';
                break;
            case 'YQ':
                $res = 'DATEPART(QQ,'.$DataColumn.')';
                break;
            case 'YW'://周数
                $res = 'DATEPART(WW,'.$DataColumn.')';
                break;
            case 'HH':
                $res = 'DATEPART(WW,'.$DataColumn.')';
                break;
            case 'D':
            default :
                $res = $DataColumn;
                break;
        }
        return $res;
    }
    
    
    /**
     * @用于处理多表时具有计算功能的字段表达式
     * @param type $Expression
     * @param type $rulesArr
     * @return string
     */
    private function setCoditionColumnExp($Expression,$AxisConfig){
        $res='';
        if(strpos($Expression,'/')){
            $val = explode('/', $Expression);
            $de = '/';
        }elseif(strpos($Expression,'*')){
            $val = explode('*', $Expression);
            $de = '*';
        }elseif(strpos($Expression,'+')){
            $val = explode('+', $Expression);
            $de = '+';
        }elseif(strpos($Expression,'-')){
            $val = explode('-', $Expression);
            $de = '-';
        }
        $key1 = strtoupper($val[0]);
        $key2 = strtoupper($val[1]);
        
        $res = $AxisConfig[$key1]['DataColumn'].$de.$AxisConfig[$key2]['DataColumn'];
        
        return $res;
    }
    
    
    /**
     * @将值追加'号
     * @param type $str 字符串或数组
     * @return type 字符串
     */
    private function getAddQuotation($str){
        $col = array();
        if(is_string($str)){
            $array=  explode(',', $str);
            foreach($array as $val){
                $col[]="'".$val."'";
            }
        }elseif(is_array($str)){
            foreach($str as $val){
                $col[] = "'".$val."'";
            }
        }
        $string=  implode($col, ',');
        
        return $string;
    }

    
    /**
     * @处理区间段值时的表达式
     * @param type $column
     * @param type $val 数组，区间段
     * @return string
     */
    private function setBetweenExp($column,$val,$FieldType){
        $s='';
        switch ($FieldType){
            case 2://日期
                $b = $val[0];
                $e = $val[1];
                if(!empty($b) && !empty($e)){
                    $s = $column." between '".$b."' and '".$e."'"; 
                }elseif(empty($b) && !empty($e)){
                    $s = $column." <= '".$e."'"; 
                }elseif(!empty($b) && empty($e)){
                    $s = $column." >= '".$b."'"; 
                }
                break;
            case 3:
                $b = empty($val[0])?0:$val[0];
                $e = empty($val[1])?0:$val[1];

                if(strpos($column,'/')){
                    $c = explode('/', $column);

                    if($b>0 && $e>0){
                        $s = $c[0].' between '.$c[1].'*'.$b.' and '.$c[1].'*'.$e;
                    }elseif($b==0 && $e>0){
                        $s = $c[0].' <= '.$c[1].'*'.$e; 
                    }elseif($b>0 && $e==0){
                        $s = $c[0].' >= '.$c[1].'*'.$b; 
                    }            
                }else{
                    if($b>0 && $e>0){
                        $s = $column.' between '.$b.' and '.$e; 
                    }elseif($b==0 && $e>0){
                        $s = $column.' <= '.$e; 
                    }elseif($b>0 && $e==0){
                        $s = $column.' >= '.$b; 
                    }
                }
                break;
        }
        return $s;
    }

    /**
     * @清除掉条件数组中的空值条件
     * @param type $array
     * @return type
     */
    protected function setWipeNull($array){
        $where=array();
        
        foreach($array as $k=>$val){
            $key = strtoupper($k);
            if(is_array($val)){
                $v = array_filter($val);
            }else{
                $v = $val;
            }
            
            if(!empty($v)){
                $where[$key] = $val;
            }
        }
        return $where;
    }

    /**
     * @说明：根据配置表解析字段对应的真实数据表的表达式（将代号翻译）
     * @param type $currentConfig 当前配置信息
     * @return type
     */
    protected function getCoditionExp($key,$AxisConfig){
        $str='';
        $Expression = $AxisConfig[$key]['Expression'];
        $DataColumn = $AxisConfig[$key]['DataColumn'];
        
        switch ($AxisConfig[$key]['FieldType']){
            case 1://对照
            case 4://文本
            default :
                $str = $DataColumn;
                break;
            case 2://日期
                $str = $this->setDateColumn($Expression,$DataColumn);
                break;
            case 3://数字
                //优先考虑表达式
                if(!empty($Expression)){
                    $str = $this->setCoditionColumnExp($Expression,$AxisConfig);
                }else{
                    $str = $DataColumn;
                }
                break;
        }
        
        return $str;
    }

    /**
     * @根据参数处理成where子句：当为复杂条件表达式时，也作为一切条件表达式的基础方法
     * @param type $key 键
     * @param type $relation 关系
     * @param type $val 值
     * @param type $isMultiSheet 是否多表
     * @param type $rulesArr 配置规则
     * @return type 字符串表达式
     */
    protected function getComplexCodition($column,$relation='EQ',$val,$FieldType,$ContrastConfig){
        $rel = strtoupper($relation);
        
        switch ($rel){
            case 'EQ':
            case 'EQUAL'://等于
                $value = is_array($val)?implode(',', $val):$val;
                if(strpos($column,'/')){
                    $e = explode('/', $column);
                    $s = $e[0].' = '.$e[1].'*'.$value;
                }else{
                    switch ($FieldType){//2及3类无模糊查询的需求
                        case 1:
                        case 3:
                            $s = $column.' = '.$value;
                            break;
                        case 2:
                        case 4:
                            $s = $column." = '".$value."'";
                            break;
                    }
                }
                break;
            case 'NOTEQUAL'://不等于
            case 'NEQ':
                $value = is_array($val)?implode(',', $val):$val;
                if(strpos($column,'/')){
                    $e = explode('/', $column);
                    $s = $e[0].' <> '.$e[1].'*'.$value;
                }else{
                    switch ($FieldType){//2及3类无模糊查询的需求
                        case 1:
                        case 3:
                            $s = $column.' <> '.$value;
                            break;
                        case 2:
                        case 4:
                            $s = $column." <> '".$value."'";
                            break;
                    }
                }
                break;
            case 'LIKE'://模糊查询
                $value = is_array($val)?implode(',', $val):$val;
                
                switch ($FieldType){//2及3类无模糊查询的需求
                    case 1:
                        $s = $column.' in ('.'select '.$ContrastConfig['ContrastId'].' from '.$ContrastConfig['ContrastSheet'].' where '.$ContrastConfig['ContrastName']." like '%".$value."%')";
                        break;
                    case 4:
                        $s = $column." like '%".$value."%'";
                        break;
                }
                break;
            case 'NOTLIKE'://模糊查询
                $value = is_array($val)?implode(',', $val):$val;
                
                switch ($FieldType){//2及3类无模糊查询的需求
                    case 1:
                        $s = $column.' in ('.'select '.$ContrastConfig['ContrastId'].' from '.$ContrastConfig['ContrastSheet'].' where '.$ContrastConfig['ContrastName']." not like '%".$value."%')";
                        break;
                    case 4:
                        $s = $column." not like '%".$value."%'";
                        break;
                }
                break;
            case 'INCLUDED'://包含
            case 'IN':
                switch ($FieldType){
                    case 1://对照
                        $value = is_array($val)?implode(',', $val):$val;
                        $s = $column.' in ('.$value.')';
                        break;
                    case 2://日期
                    case 4://文本
                        $s = $column.' in ('.$this->getAddQuotation($val).')';
                        break;
                }
                break;
            case 'NOTINCLUDED'://包含    
            case 'NOTIN':
                switch ($FieldType){
                    case 1://对照
                        $value = is_array($val)?implode(',', $val):$val;
                        $s = $column.' not in ('.$value.')';
                        break;
                    case 2://日期
                    case 4://文本
                        $s = $column.' not in ('.$this->getAddQuotation($val).')';
                        break;
                }
                break;
            case 'BETWEEN'://包含    
            case 'BT':
                if(is_array($val)){
                    $s = $this->setBetweenExp($column,$val,$FieldType);                    
                }elseif(is_string($val)){
                    $arr=  explode(',', $val);
                    $s = $this->setBetweenExp($column,$arr,$FieldType);
                }
                break;
        }
        return $s;
    }
    
    
    /**
     * @简单条件表达符：简单的K-V结构[V为数组]
     * @param type $key
     * @param type $val
     * @param type $isMultiSheet
     * @param type $rulesArr
     * @return type
     */
    protected function getSimpleCodition($column,$val,$FieldType,$ContrastConfig){
        switch ($FieldType){
            case 1://对照ID
                $s = $this->getComplexCodition($column,'IN',$val,$FieldType,$ContrastConfig);
                break;
            case 2://如年份
            case 3://数字区间
                $s = $this->getComplexCodition($column,'BT',$val,$FieldType,$ContrastConfig);
                break;
            case 4://文本
                $s = $this->getComplexCodition($column,'LIKE',$val,$FieldType,$ContrastConfig);
                break;
        }        
        return $s;
    }
    
    /**
     * @简单条件表达符：简单的K-V结构[V为字符串]--与getSimpleCodition有点区别
     * @param type $key
     * @param type $val
     * @param type $isMultiSheet
     * @param type $rulesArr
     * @return type
     */
    protected function getFastCodition($column,$val,$FieldType,$ContrastConfig){
        $s = $this->getComplexCodition($column,'EQ',$val,$FieldType,$ContrastConfig);
        return $s;
    }
    
    
     /**
      * @方法：根据选择的单位编号，计算出数据表达式，主要用于区间段时按单位计算
      * @param type $uom
      * @return type
      */
     protected function setUomRateFormats($uom,$uomArr){
        if(empty($uom)){
            $rate = 1;
        }else{
            //1.主计量单位
            if($uomArr[$uom]['IsMain']==1){
                $rate = 1;
            }else{//2.非主计量单位
                $rate = $uomArr[$uom]['Rate'];
            }
        }
        
        
        return $rate;
    }
    
    /**
     * @方法：根据键值及相对应的区间段
     * @param type $regArr
     * @param type $rate 换算率
     * @param type $uom 单位名称
     * @return string
     */
    protected function setRegionDetail($regArr,$rate,$uom){
        $i=1;//用于判断首次
        $resArr=array();//记录各区间段的ID及TITLE

        if(is_string($regArr)){
            $regArr = explode('|', $regArr);
        }
        
        foreach($regArr as $valArr){
            $aArr=  explode('-', $valArr);
            
            $min=$aArr[0]*$rate;
            $max=$aArr[1]*$rate;
                     
            $minName = $aArr[0].$uom;
            $maxName = $aArr[1].$uom;
                        
            $rName=$aArr[0].'-'.$aArr[1].$uom;
            
            if($i==1){
                if($min>0){//最小值不为0
                    $resArr[$i]['min']=0;
                    $resArr[$i]['max']=$min;
                    $resArr[$i]['title']='<='.$minName;

                    $i++;
                    $resArr[$i]['min']=$min;
                    $resArr[$i]['max']=$max;
                    $resArr[$i]['title']=$rName;
                }else{//最小值为0
                    $resArr[$i]['min']=0;
                    $resArr[$i]['max']=$max;
                    $resArr[$i]['title']='<='.$maxName;
                }
            }else{
               $resArr[$i]['min']=$min;
               $resArr[$i]['max']=$max;
               $resArr[$i]['title']=$rName;
            }                
            $i++;
        }
        //最后追加最大区间段
        $resArr[$i]['min']=$max;
        $resArr[$i]['max']=0;
        $resArr[$i]['title']='>'.$maxName;
        
        return $resArr;
    }
    
    
    
    /**
     * @方法：根据字段名、单位、统计规则拼凑统计字段格式
     * @param type $key
     * @param type $count
     * @return type
     */
    protected function setCountColumnFormats($key,$count){
        return $key.'_'.$count;
    }
    
    
    /**
     * @解析SELECT字段的表达式，兼容各种类型的字段：非统计字段
     * @param type $FieldType
     * @param type $Expression
     * @param type $DataColumn
     * @param type $region
     * @param type $Special
     * @return string
     */
    protected function getSelectColumnExp($FieldType,$Expression,$DataColumn,$region=array(),$Special=''){
        $str='';
        
        switch ($FieldType){
            case 1:
            default :
                if(!empty($Special)){
                    $str = $Special;
                }else{
                    $str = $DataColumn;
                }                
                break;
            case 2://日期
                if(!empty($Special)){
                    $str = "'".$Special."'";
                }else{
                    $str = $this->setDateExp($Expression,$DataColumn);
                }
                break;
            case 3://数字字段
                $str = $this->setNumberExp($DataColumn,$region);
                //带区间段时，需要转义
                $key = $key.'_A';
                break;
            case 4:
                if(!empty($Special)){
                    $str = "'".$Special."'";
                }else{
                    $str = $DataColumn;
                }
                break;
        }    
        
        //增加别名转义
        $str .= ' as '.$key;
        return $str;
    }
    
    private function setCountParseExp($delimiter,$string,$rulesArr){
        $val = explode($delimiter, $string);
        $k1 = strtoupper($val[0]);
        $k2 = strtoupper($val[1]);
        
        if($isMultiSheet){
            $DataColumn=$rulesArr[$k1]['DataSheet'].'.'.$rulesArr[$k1]['DataColumn'].' as '.$this->setColumnBynameFormat($rulesArr[$k1]['DataSheet'],$rulesArr[$k1]['DataColumn'])
                        .','.$rulesArr[$k2]['DataSheet'].'.'.$rulesArr[$k2]['DataColumn'].' as '.$this->setColumnBynameFormat($rulesArr[$k2]['DataSheet'],$rulesArr[$k2]['DataColumn']);
        }else{
            $DataColumn=$rulesArr[$k1]['DataColumn'].','.$rulesArr[$k2]['DataColumn'];
        }
        return $DataColumn;
    }
    

    /**
     * @用于数据源读取时字段别名(多表时)
     * @param type $table
     * @param type $column
     * @return type
     */
    private function setColumnBynameFormat($table,$column){
        return $table.$column;
    }
    
    private function setColumnResultFormat($key,$count){
        return $key.'_'.$count;
    }

    /**
     * @解析统计字段的表达方式：用于解析数据源字段
     * @param type $isMultiSheet
     * @param type $rulesArr
     * @param type $val
     * @return type
     */
    protected function getCountColumnExp($Expression,$key,$rulesArr){
        $str='';
        if(strpos($Expression,'/')){
            $DataColumn=$this->setCountParseExp('/',$Expression,$rulesArr);
        }elseif(strpos($rulesArr[$key]['Expression'],'*')){
            $DataColumn=$this->setCountParseExp($isMultiSheet,'*',$Expression,$rulesArr);
        }elseif(strpos($rulesArr[$key]['Expression'],'+')){
            $DataColumn=$this->setCountParseExp($isMultiSheet,'+',$Expression,$rulesArr);
        }elseif(strpos($rulesArr[$key]['Expression'],'-')){
            $DataColumn=$this->setCountParseExp($isMultiSheet,'-',$Expression,$rulesArr);
        }else{
            if($isMultiSheet){
                $DataColumn=$rulesArr[$key]['Expression'].' as '.$this->setColumnBynameFormat($rulesArr[$key]['DataSheet'],$key);
            }else{
                $DataColumn=$Expression.' as '.$key;
            }                
        }
        if(empty($DataColumn)){
            
        }else{
            if($isMultiSheet){
                $DataColumn=$rulesArr[$key]['DataSheet'].'.'.$rulesArr[$key]['DataColumn'].' as '.$this->setColumnBynameFormat($rulesArr[$key]['DataSheet'],$key);
            }else{
                $DataColumn=$DataColumn;
            }            
        }
        
        return $DataColumn;
    }
    
    /**
     * @方法：处理一些日期常量的表达式,主要用于解析时间相关的表达式
     * @param type $exp 表达式 Expression
     * @param type $arr 字段值 DataColumn
     * @return string
     */
    protected function setDateExp($key,$arr){
        switch ($key){
            case 'Y'://年份
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'年'";
                break;
            case 'YM'://年月
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'年'+RIGHT('00'+CAST(MONTH(".$arr.") AS VARCHAR(2)),2)+'月'";
                break;
            case 'YW'://年周
                $res = "CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0,DateAdd(dd,-1,".$arr.")),0),120)";//周一作为周的第一天,主要注意周日要减一天
                $res .= "+'至'+";
                $res .= "CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0,DateAdd(dd,-1,".$arr.")),6),120)";//周日作为周的最后一天

                break;
            case 'YQ'://年季
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'年第'+CONVERT(nvarchar(2),DATEPART(QQ,".$arr."))+'季度'";
                break;
            case 'YB'://半年
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'年'+(case when MONTH(".$arr.")<=6 then '上半年' else '下半年' end)";
                break;
            case 'HH'://小时24小时制
                $res = "CONVERT(nvarchar(2),datepart(hh,".$arr."))";
                break;
            case 'D'://日
            default :
                $res = "CONVERT(nvarchar(10),".$arr.")";
                break;
        }
        return $res;
    }
    
    /**
     * @方法：处理一些日期常量的表达式,主要用于解析时间相关的表达式[此处主要用于转换日期的基础表达式]
     * @param type $exp 表达式 Expression
     * @param type $arr 字段值 DataColumn
     * @return string
     */
    protected function setDateCodeExp($key,$arr){
        switch ($key){
            case 'Y'://年份
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))";
                break;
            case 'YM'://年月
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'-'+RIGHT('00'+CAST(MONTH(".$arr.") AS VARCHAR(2)),2)";
                break;
            case 'YW'://年周
                $res = "CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0,DateAdd(dd,-1,".$arr.")),0),120)";//周一作为周的第一天,主要注意周日要减一天
                $res .= "+'|'+";
                $res .= "CONVERT(varchar(10),DATEADD(wk, DATEDIFF(wk,0,DateAdd(dd,-1,".$arr.")),6),120)";//周日作为周的最后一天

                break;
            case 'YQ'://年季
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'-'+CONVERT(nvarchar(2),DATEPART(QQ,".$arr."))";
                break;
            case 'YB'://半年
                $res = "CONVERT(nvarchar(4),YEAR(".$arr."))+'-'+(case when MONTH(".$arr.")<=6 then '1' else '2' end)";
                break;
            case 'HH'://小时24小时制
                $res = "CONVERT(nvarchar(2),datepart(hh,".$arr."))";
                break;
            case 'D'://日
            default :
                $res = "CONVERT(nvarchar(10),".$arr.")";
                break;
        }
        return $res;
    }
    
    /**
     * @处理数字字段的表达式：根据配置表表达式找到相应的源字段：区间段时的字段表达式
     * @param type $isMultiSheet
     * @param type $key
     * @param type $rulesArr
     * @return type
     */
    private function setCountExp($isMultiSheet,$key,$rulesArr){
        $key = strtoupper($key);
        if(empty($rulesArr[$key]['DataColumn'])){
            $exp = $rulesArr[$key]['Expression'];
        }else{
            $exp = $rulesArr[$key]['DataColumn'];
        }
                
        if(strpos($exp,'/')){
            $res=  explode('/', $exp);
            $key1 = strtoupper($res[0]);//分子
            $key2 = strtoupper($res[1]);//分母
            if($isMultiSheet){
                $numerator=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key1]['DataColumn'];
                $denominator=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key2]['DataColumn'];
            }else{
                $numerator=$rulesArr[$key1]['DataColumn'];
                $denominator=$rulesArr[$key2]['DataColumn'];
            }
            $column = '(case when '.$denominator.'>0'.' then '.$numerator.'/'.$denominator.' else 0 end)';
        }elseif(strpos($exp,'+')){
            $res=  explode('+', $exp);
            $key1 = strtoupper($res[0]);//第一
            $key2 = strtoupper($res[1]);//第二
            if($isMultiSheet){
                $first=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key1]['DataColumn'];
                $second=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key2]['DataColumn'];
            }else{
                $first=$rulesArr[$key1]['DataColumn'];
                $second=$rulesArr[$key2]['DataColumn'];
            }
            $column = $first.'+'.$second;
        }elseif(strpos($exp,'-')){
            $res=  explode('-', $exp);
            $key1 = strtoupper($res[0]);//第一
            $key2 = strtoupper($res[1]);//第二
            if($isMultiSheet){
                $first=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key1]['DataColumn'];
                $second=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key2]['DataColumn'];
            }else{
                $first=$rulesArr[$key1]['DataColumn'];
                $second=$rulesArr[$key2]['DataColumn'];
            }
            $column = $first.'-'.$second;
        }elseif(strpos($exp,'*')){
            $res=  explode('*', $exp);
            $key1 = strtoupper($res[0]);//第一
            $key2 = strtoupper($res[1]);//第二
            if($isMultiSheet){
                $first=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key1]['DataColumn'];
                $second=$rulesArr[$key1]['DataSheet'].'.'.$rulesArr[$key2]['DataColumn'];
            }else{
                $first=$rulesArr[$key1]['DataColumn'];
                $second=$rulesArr[$key2]['DataColumn'];
            }
            $column = $first.'*'.$second;
        }else{
            if($isMultiSheet){
                $column = $rulesArr[$key]['DataSheet'].'.'.$exp;
            }else{
                $column = $exp;
            }
        }
        return $column;
    }

    /**
     * @解析区间段表达式
     * @param type $column
     * @param type $regArr
     * @return string
     */
    protected function setNumberExp($column,$regArr){        
        $resString='(case ';
        
        foreach ($regArr as $key=>$val){
            if($val['min']==0&&$val['max']>0){
                $resString.=' when '.$column.'<='.$val['max'].' then '.$key;
            }
            if($val['min']>0&&$val['max']==0){
                $resString.=' when '.$column.'>'.$val['min'].' then '.$key;
            }
            if($val['min']>0&&$val['max']>0){
                $resString.=' when '.$column.'>'.$val['min'].' and '.$column.'<='.$val['max'].' then '.$key;
            }
        }
        
        $resString.=' end)';
        
        return $resString;
    }
    
    
    /**
     * @解析SELECT字段，当统计时的表达式
     * @param type $rulesArr
     * @param type $allAxis
     * @return string
     */
    protected function setSelectColumnExp($rulesArr,$allAxis){
        $res = array();
        foreach ($allAxis as $key){
            switch ($rulesArr[$key]['FieldType']){
                case 1:
                    $res[] = $key;
                    break;
                case 2:
                    $res[] = $key;
                    $res[] = 'D_'.$key;
                    break;
                case 4:
                    $res[] = $key;
                    break;
                case 3:
                    $res[] = $key.'_A'.' as '.$key;
                    break;
            }
        }
        return implode(',', $res);
    }
    
    
    /**
     * @根据参数设置统计时字段表达式
     * @param type $isMultiSheet
     * @param type $rulesArr
     * @param type $uomArr
     * @param type $key
     * @param type $count
     * @param type $uom
     * @return string
     */
    protected function setCount($rulesArr,$uomArr,$val){
        //初始化参数
        $key = $val['k'];
        $count = $val['c'];
        $uom = $val['u'];
        
        //计量单位
        if($uom == $rulesArr[$key]['Uom']){//默认计量时，换算率为1，并忽略
            $rate = '';
        }else{
            if((int)$uomArr[$uom]['Rate']==1){
                $rate = '';
            }else{
                $rate = '/'.$uomArr[$uom]['Rate'];
            }
            
        }
        
        //字段表达式
        if(!empty($rulesArr[$key]['DataColumn'])){
            $res = $count.'('.$rulesArr[$key]['DataColumn'].')'.$rate.' as '.$this->setColumnResultFormat($key,$count);
        }else{
            if(strpos($rulesArr[$key]['Expression'],'/')){
                $arr = explode('/', $rulesArr[$key]['Expression']);
                $a = strtoupper($arr[0]);
                $b = strtoupper($arr[1]);
                
                $k1 = $rulesArr[$a]['DataColumn'];
                $k2 = $rulesArr[$b]['DataColumn'];
                
                $le = 'SUM'.'('.$k1.')'.'/'.'SUM'.'('.$k2.')'.$rate;
            }elseif(strpos($rulesArr[$key]['Expression'],'*')){
                $arr = explode('*', $rulesArr[$key]['Expression']);
                $a = strtoupper($arr[0]);
                $b = strtoupper($arr[1]);
                
                $k1 = $rulesArr[$a]['DataColumn'];
                $k2 = $rulesArr[$b]['DataColumn'];
                
                $le = $count.'('.$k1.')'.'*'.$count.'('.$k2.')'.$rate;
            }elseif(strpos($rulesArr[$key]['Expression'],'+')){
                $arr = explode('+', $rulesArr[$key]['Expression']);
                $a = strtoupper($arr[0]);
                $b = strtoupper($arr[1]);
                
                $k1 = $rulesArr[$a]['DataColumn'];
                $k2 = $rulesArr[$b]['DataColumn'];
                
                $le = '('.$count.'('.$k1.')'.'+'.$count.'('.$k2.')'.')'.$rate;
            }elseif(strpos($rulesArr[$key]['Expression'],'-')){
                $arr = explode('-', $rulesArr[$key]['Expression']);
                $a = strtoupper($arr[0]);
                $b = strtoupper($arr[1]);
                
                $k1 = $rulesArr[$a]['DataColumn'];
                $k2 = $rulesArr[$b]['DataColumn'];
                    
                $le = '('.$count.'('.$k1.')'.'-'.$count.'('.$k2.')'.')'.$rate;
            }else{
                $le = $count.'('.$key.')'.$rate;
            }
            
            $res = $le.' as '.$this->setColumnResultFormat($key,$count);
        }
        return $res;
    }
    
    
    /**
     * @将占比结果解析成字段表达式
     * @param type $column
     * @param type $total
     */
    protected function setRateCount($key,$column,$totalSql){
        return '100*sum'.'('.$column.')'.'/'.'('.$totalSql.')'.' as '.$this->setColumnResultFormat($key,'RATE');
    }


    /**
     * @设置GROUP BY 字段的表达式
     * @param type $rulesArr
     * @param type $allAxis
     * @return type
     */
    protected function setGroupByColumnExp($rulesArr,$allAxis){
        $res = array();
        foreach ($allAxis as $key){
            switch ($rulesArr[$key]['FieldType']){
                case 1:
                    $res[] = $key;
                    break;
                case 2:
                    $res[] = $key;
                    $res[] = 'D_'.$key;
                    break;
                case 4:
                    $res[] = $key;
                    break;
                case 3:
                    $res[] = $key.'_A';
                    break;
            }
        }
        return implode(',', $res);
    }
    
    /**
     * @将多维数组提取键名，组成一个数组
     * @param type $vAxis
     * @return type
     */
    protected function getArrayKeys($vAxis){
        $res = array();
        foreach ($vAxis as $val){
            $res[] = $this->setCountColumnFormats($val['k'], $val['c']);
        }
        return $res;
    }
    
    /**
     * @解析统计字段显示名称
     * @param type $val
     * @return type
     */
    protected function parseCountName($val,$rulesArr,$CountConfig,$UomConfig){
        
        $columnName = $rulesArr[$val['k']]['FieldName'];
        $countName = $CountConfig[$val['c']]['CountName'];
        
        if($val['c']=='RATE'){
            $uomName = '%';
        }else{
            $uomName = $UomConfig[$val['u']]['UomName'];
        }        
        
        return $columnName.'('.$countName.','.$uomName.')';
    }
    
    /**
     * @设置表头数组显示格式
     * @param type $title
     * @param type $field
     * @param type $id
     * @param type $colspan
     * @return type
     */
    protected function setHeaderDataFormat($title,$field,$id,$colspan=1){
        //title显示名称
        if(!empty($title)){
            $res['t']=$title;
        }
        //field字段代号
        if(!empty($field)){
            $res['f']=$field;
        }
        //id字段ID号
        if(!empty($id)){
            $res['i']=$id;
        }
        //colspan数量
        if($colspan>1){
            $res['c']=$colspan;
        }
        return $res;
    }
    
    
    /**
     * @根据当前统计字段表达式，以及数据精度配置表，返回数据精度值
     * @param type $AccuracyConfig
     * @param type $vAxis
     * @return type
     */
    protected function setNumberAccuracy($AccuracyConfig,$vAxis){
        $Accuracy=0;
        $result = array();
        foreach($vAxis as $val){
            $key = $val['k'].'_'.$val['c'];
            if($val['c']=='RATE'){
                $result[$key] = 2;
            }else{
                $result[$key] = $AccuracyConfig[$val['k']];
            }
        }
        return $result;
    }
    
    
    /**
     * @根据表头数据，格式化成表头数组表达式
     * @param type $level
     * @param type $Lhead
     * @param type $Mhead
     * @param type $Rhead
     * @return type
     */
    protected function setHeaderLevelArray($level,$Lhead,$Mhead,$Rhead=array(),$isSingle=1){
        $data=array();
        $Ldata=array();
        $Rdata=array();
        
        if(empty($Rhead)){
            $right = false;
        }else{
            $right = true;
        }
        
        if($level > 1){//多层            
            //将左侧表头结果拆成层数组
            for($i=0;$i < $level;$i++){
                if($i == $level - 1){
                    foreach ($Lhead as $key=>$val){
                        $data[$i][] = $this->setHeaderDataFormat($val['t'],$val['f'],$key,$val['c']);
                    }
                }else{
                    foreach ($Lhead as $key=>$val){
                        $Ldata[$i][$key] = array('f'=>$val['f'],'t'=>$val['t'],'c'=>1);
                    }                    
                }                
            }
            
            //将表头结果拆成层数组
            foreach ($Mhead as $key=>$val){
                $Narr = explode('@', $val['t']);
                for($i=0;$i < $level;$i++){
                    //最后一层
                    if($i == $level - 1){
                        $data[$i][] = $this->setHeaderDataFormat($Narr[$i],$val['f'],$key,$val['c']);
                    }else{
                        $data[$i][] = $Narr[$i];
                    }
                }
            }
            
            //右侧表头拆成数组
            if($right){
                $j=0;
                
                foreach ($Rhead as $key=>$val){
                    $Narr = explode('@', $val['t']);                  
                    
                    for($i=0;$i < $level;$i++){
                        //最后一层
                        if($i == $level - 1){
                            $data[$i][] = $this->setHeaderDataFormat($Narr[$i],$val['f'],$val['f'],$val['c']);
                        }else{
                            $Rdata[$i]['sums'] = array('f'=>'sums','t'=>$Narr[$i],'c'=>$isSingle);
                        }
                    }
                }    
            }            
            
            //对非最底层数组进行去重及格式化
            for($i=0;$i < $level - 1;$i++){
                //计算每个值重复的次数
                $Lines = array_count_values($data[$i]);
                
                //清除此层数据
                unset($data[$i]);
                
                //追加左侧表头
                foreach ($Ldata[$i] as $key=>$val){
                    $data[$i][]=$this->setHeaderDataFormat($val['t'],$val['f'],'',$val['c']);
                }                
                
                //对去重后的数组进行格式化
                foreach ($Lines as $title=>$colspan){
                    $data[$i][]=$this->setHeaderDataFormat($title,'','',$colspan);
                }
                
                //追加左侧表头
                if($right){
                    foreach ($Rdata[$i] as $key=>$val){
                        $data[$i][]=$this->setHeaderDataFormat($val['t'],$val['f'],'',$val['c']);
                    }                    
                }
            }
            
            //对表头数组进行排序
            asort($data);
        }else{//单层
            //左侧
            foreach ($Lhead as $key=>$val){
                $data[0][]=$this->setHeaderDataFormat($val['t'],$val['f'],$key,$val['c']);
            }
            //中间
            foreach ($Mhead as $key=>$val){
                $data[0][]=$this->setHeaderDataFormat($val['t'],$val['f'],$key,$val['c']);
            }
            //右侧
            foreach ($Rhead as $key=>$val){
                $data[0][]=$this->setHeaderDataFormat($val['t'],$val['f'],$key,$val['c']);
            }            
        }
        return $data;
    }
    
    
    /**
     * @根据数据的统计方式，求合计
     * @param type $type
     * @param type $array
     * @return type
     */
    protected function setCountValues($type,$array){
        $nums = 0;
        switch ($type){
            case 'SUM':
            case 'RATE':
            default :
                $nums = array_sum($array);
                break;
            case 'AVG':            
                $nums = array_sum($array)/count($array);
                break;
            case 'MAX':
                $nums = max($array);
                break;
            case 'MIN':
                $nums = min($array);
                break;
        }
        return $nums;
    }
    
    
    /**
     * @格式化输出数据的精度值
     * @param type $key
     * @param type $num
     * @return type
     */
    protected function setNumberFormat($key,$num){
        $num = empty($num)?0:$num;
        return round($key,$num);
    }
    
    
    /**
     * @方法：根据字段代号的首位字母，返回数据精度
     * @参数：数字
     * @返回：格式化的数字
     */
    protected function setCodeAccuracy($key,$value){
        switch (substr($key,0,1)){
            case 'A':
                $result = round($value,0);
                break;
            case 'B':
                $result = round($value,1);
                break;
            case 'C':
                $result = round($value,2);
                break;
            case 'D':
                $result = round($value,3);
                break;
            case 'E':
                $result = round($value,4);
                break;
            case 'F':
                $result = round($value,5);
                break;
            case 'G':
                $result = round($value,6);
                break;
            case 'H':
                $result = round($value,7);
                break;
            case 'I':
                $result = round($value,8);
                break;
            case 'J':
                $result = round($value,9);
                break;
            case 'K':
                $result = round($value,10);
                break;
            default :
                $result = $value;
                break;
        }
        return $result;
    }
    
    /**
     * @方法：根据统计方式关键字，判断代号字母，主要用于控制显示的小数位
     * @参数：统计方式
     */
    protected function setCountCode($key,$num=0){
        $base=array('A','B','C','D','E','F','G','H','I','J','K');//最大10位小数
        
        if(strpos($key,'_')){
            $str = explode('_', $key);
            $key=$str[0];
        }
        
        $code = $base[$num];
        
        return $code;
    }
    
    /**
     * @将表头数组处理成交叉表需要的表达式
     * @param type $head
     * @return type
     */
    protected function setPivotExp($head){
        $p = array();//字段表达式
        $h = array();//结果表达式
        $r = array();//代号表达式
        foreach ($head as $key=>$val){
            $p[] = '['.$key.']';
            $h[] = '['.$key.']'.' as '.$val['f'];
            $r[] = $val['f'];
        }
        
        return array('p'=>$p,'h'=>$h,'r'=>$r);
    }
}
